package com.kcsj.dao.User;

import com.kcsj.domain.user.Employee;
import com.kcsj.domain.user.Role;
import com.kcsj.domain.user.User;
import com.kcsj.util.Condition;
import com.kcsj.util.JdbcHelper;

import java.sql.*;
import java.util.ArrayList;
import java.util.Collection;
import java.util.List;

/**
 * @author 胡庆阳
 * 29th Nov. 2020
 */
public class EmployeeDao {
    private static final EmployeeDao EMPLOYEE_DAO = new EmployeeDao();
    private EmployeeDao() {
    }

    public static EmployeeDao getInstance() {
        return EMPLOYEE_DAO;
    }
    //查找school表中所有数据
    public Collection<User> findAll(Connection conn, List<Condition> conditionList) throws SQLException {
        ArrayList<User> users = new ArrayList<>();
        //预编译方式进行查询
        StringBuilder s = new StringBuilder( "select * from user,employee where user.id = employee.id");
        if(conditionList!=null){
            String whereClause = Condition.toWhereClause2(conditionList);
            s.append(whereClause);
        }
        System.out.println(s.toString());
        PreparedStatement preparedStatement = conn.prepareStatement(s.toString());
        ResultSet resultSet = preparedStatement.executeQuery();
        while (resultSet.next()){
            int id = resultSet.getInt("id");
            String username = resultSet.getString("username");
            String password = resultSet.getString("password");
            String type = resultSet.getString("type");
            String no = resultSet.getString("no");
            int role_id = resultSet.getInt("role_id");
            Boolean activationStatus = resultSet.getBoolean("activationStatus");
            String name = resultSet.getString("name");
            Integer age = resultSet.getInt("age");
            String sex = resultSet.getString("sex");
            String phoneNumber = resultSet.getString("phoneNumber");
            String email = resultSet.getString("email");
            String IDCard = resultSet.getString("IDCard");
            Role role = RoleDao.getInstance().find(role_id,conn);
            if(age==0){
                age=null;
            }
            Employee employee = new Employee(id,no,name,age,sex,phoneNumber,email,IDCard);
            User user = new User(id, username, password, type, role, activationStatus,employee);
            users.add(user);
        }
        JdbcHelper.close(resultSet,preparedStatement);
        return users;
    }


    //根据id值查找DeliveryPerson表中相应数据
    public User find(Integer id, Connection conn) throws SQLException {
        User user = null;
        //预编译方式进行查询
        String s = "select * from user,employee where  user.id = employee.id and employee.id = ?";
        PreparedStatement preparedStatement = conn.prepareStatement(s);
        preparedStatement.setInt(1,id);
        ResultSet resultSet = preparedStatement.executeQuery();
        //将resultSet转换为json字符串
        if (resultSet.next()){
            String username = resultSet.getString("username");
            String password = resultSet.getString("password");
            String type = resultSet.getString("type");
            String no = resultSet.getString("no");
            int role_id = resultSet.getInt("role_id");
            Boolean activationStatus = resultSet.getBoolean("activationStatus");
            String name = resultSet.getString("name");
            Integer age = resultSet.getInt("age");
            String sex = resultSet.getString("sex");
            String phoneNumber = resultSet.getString("phoneNumber");
            String email = resultSet.getString("email");
            String IDCard = resultSet.getString("IDCard");
            if(age==0){
                age=null;
            }
            Role role = RoleDao.getInstance().find(role_id,conn);
            Employee employee = new Employee(id,no,name,age,sex,phoneNumber,email,IDCard);
            user = new User(id, username, password, type, role, activationStatus,employee);
        }
        //关闭连接
        JdbcHelper.close(resultSet,preparedStatement);
        return user;
    }

    public Employee findInfo(Integer id, Connection conn) throws SQLException {
        Employee employee = null;
        //预编译方式进行查询
        String s = "select * from employee where id = ?";
        PreparedStatement preparedStatement = conn.prepareStatement(s);
        preparedStatement.setInt(1,id);
        ResultSet resultSet = preparedStatement.executeQuery();
        //将resultSet转换为json字符串
        if (resultSet.next()){
            String no = resultSet.getString("no");
            String name = resultSet.getString("name");
            Integer age = resultSet.getInt("age");
            String sex = resultSet.getString("sex");
            String phoneNumber = resultSet.getString("phoneNumber");
            String email = resultSet.getString("email");
            String IDCard = resultSet.getString("IDCard");
            if(age==0){
                age=null;
            }
            employee = new Employee(id,no,name,age,sex,phoneNumber,email,IDCard);
        }
        //关闭连接
        JdbcHelper.close(resultSet,preparedStatement);
        return employee;
    }

    //修改school表中的数据
    public boolean update(Employee employee, Connection conn) throws SQLException {
        //预编译方式进行修改数据
        String s = "update employee set no = ?,name = ?,age = ?,sex = ?,phoneNumber = ?,email = ?,IDCard = ? where id = ?";
        PreparedStatement preparedStatement = conn.prepareStatement(s);
        preparedStatement.setString(1, employee.getNo());
        preparedStatement.setString(2, employee.getName());
        preparedStatement.setInt(3, employee.getAge());
        preparedStatement.setString(4, employee.getSex());
        preparedStatement.setString(5, employee.getPhoneNumber());
        preparedStatement.setString(6, employee.getEmail());
        preparedStatement.setString(7, employee.getIDCard());
        preparedStatement.setInt(8,employee.getId());
        //如果执行影响行数大于0，则修改成功
        int i = preparedStatement.executeUpdate();
        //关闭连接
        JdbcHelper.close(preparedStatement);
        return i > 0;
    }
    //向DeliveryPerson表中添加数据
    public boolean add(User user, Connection conn) throws SQLException {
        //预编译方式进行增加数据
        Employee employee = (Employee)user.getActor();
        String spl = "CALL addEmployee(?,?,?,?,?,?,?,?,?,?)";
        CallableStatement callableStatement = conn.prepareCall(spl);
        callableStatement.setString(1,user.getPassword());
        callableStatement.setString(2, user.getType());
        callableStatement.setInt(3, user.getRole().getId());
        callableStatement.setBoolean(4, user.getActivationStatus());
        callableStatement.setString(5, employee.getName());
        if (employee.getAge() != null) {
            callableStatement.setInt(6, employee.getAge());
        }else {
            callableStatement.setString(6,null);
        }
        callableStatement.setString(7, employee.getSex());
        callableStatement.setString(8, employee.getPhoneNumber());
        callableStatement.setString(9, employee.getEmail());
        callableStatement.setString(10, employee.getIDCard());
        int rowsAffected = callableStatement.executeUpdate();
        JdbcHelper.close(callableStatement);
        return rowsAffected>0;
    }


    //在school表根据id删除相应数据
    public boolean delete(Integer user_id, Connection conn) throws SQLException {
        //预编译方式进行删除数据
        String s = "delete from employee where  id = ?";
        PreparedStatement preparedStatement = conn.prepareStatement(s);
        preparedStatement.setInt(1,user_id);
        //如果执行影响行数大于0，则增加成功
        int i = preparedStatement.executeUpdate();
        //关闭连接
        JdbcHelper.close(preparedStatement);
        return i > 0;
    }

}
